package trystero.lib.db.jdbc.sqlite;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import trystero.lib.db.DBClient;
import trystero.lib.db.SQLiteSchema;
import trystero.lib.component.ReaderItem;
import trystero.lib.component.ReaderLink;
import trystero.lib.component.ReaderUser;
import trystero.lib.component.ReaderComment;

public class DBClientImpl extends DBClient
{
   public boolean setup() throws Exception
   {
      Connection dbh = DriverManager.getConnection("jdbc:sqlite:test.db");
      Statement sth = dbh.createStatement();

      sth.executeUpdate(SQLiteSchema.dropTableStream());
      sth.executeUpdate(SQLiteSchema.createTableStream());

      sth.executeUpdate(SQLiteSchema.dropTableItem());
      sth.executeUpdate(SQLiteSchema.createTableItem());

      sth.executeUpdate(SQLiteSchema.dropTableLink());
      sth.executeUpdate(SQLiteSchema.createTableLink());

      sth.executeUpdate(SQLiteSchema.dropTableOrigin());
      sth.executeUpdate(SQLiteSchema.createTableOrigin());

      sth.executeUpdate(SQLiteSchema.dropTableCategory());
      sth.executeUpdate(SQLiteSchema.createTableCategory());

      sth.executeUpdate(SQLiteSchema.dropTableUser());
      sth.executeUpdate(SQLiteSchema.createTableUser());

      sth.executeUpdate(SQLiteSchema.dropTableComment());
      sth.executeUpdate(SQLiteSchema.createTableComment());

      return true;
   }

   public boolean insert(ReaderItem item) throws Exception
   {
      Connection dbh = DriverManager.getConnection("jdbc:sqlite:test.db");

      Statement s = dbh.createStatement() ;
      ResultSet r = s.executeQuery("select count(id) from item");
      while(r.next())
	 System.out.println("Item table has: " + r.getString(1));
      r.close();

      PreparedStatement sthItem = dbh.prepareStatement(
         "insert into item values (?, ?, ?, ?, ?, ?, ?, ?, ?)"
      );

      long id = fromTag(item.id());

      sthItem.setLong(1, 0); // XXX
      sthItem.setLong(2, id);
      sthItem.setLong(3, item.crawled());
      sthItem.setString(4, item.id());
      sthItem.setString(5, item.title());
      sthItem.setLong(6, item.published());
      sthItem.setLong(7, item.updated());
      sthItem.setString(8, item.summary().content());
      sthItem.setString(9, item.author());
      sthItem.addBatch();

      PreparedStatement sthOrigin = dbh.prepareStatement(
         "insert into origin values (?, ?, ?, ?)"
      );

      sthOrigin.setLong(1, id);
      sthOrigin.setString(2, item.origin().stream());
      sthOrigin.setString(3, item.origin().title());
      sthOrigin.setString(4, item.origin().url());
      sthOrigin.addBatch();

      PreparedStatement sthLink = dbh.prepareStatement(
         "insert into link values (?, ?, ?, ?, ?, ?)"
      );
          
      if(item.enclosure() != null)
         for(int i = 0; i < item.enclosure().size(); i++)
         {
            ReaderLink link = (ReaderLink)item.enclosure().get(i);
   
            sthLink.setLong(1, id);
            sthLink.setLong(2, 0);
            sthLink.setString(3, link.href());
            sthLink.setString(4, link.type());
            sthLink.setString(5, link.title());
            sthLink.setLong(6, link.length());
            sthLink.addBatch();
         }

      if(item.alternate() != null)
         for(int i = 0; i < item.alternate().size(); i++)   
         {
            ReaderLink link = (ReaderLink)item.alternate().get(i);

            sthLink.setLong(1, id);
            sthLink.setLong(2, 1);
            sthLink.setString(3, link.href());
            sthLink.setString(4, link.type());
            sthLink.setString(5, link.title());
            sthLink.setLong(6, link.length());
            sthLink.addBatch();
         }

      if(item.via() != null)
         for(int i = 0; i < item.via().size(); i++)
         { 
            ReaderLink link = (ReaderLink)item.via().get(i);

            sthLink.setLong(1, id);
            sthLink.setLong(2, 2);
            sthLink.setString(3, link.href());
            sthLink.setString(4, link.type());
            sthLink.setString(5, link.title());
            sthLink.setLong(6, link.length());
            sthLink.addBatch();
         }

      PreparedStatement sthUser = dbh.prepareStatement(
         "insert into user values (?, ?)"
      );  
      if(item.liked() != null)
         for(int i = 0; i < item.liked().size(); i++)
         {
            ReaderUser user = (ReaderUser)item.liked().get(i);
          
            sthUser.setLong(1, id);
            sthUser.setString(2, user.user());
            sthUser.addBatch();
         }
          
      PreparedStatement sthComment = dbh.prepareStatement(
         "insert into comment values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
      );
      if(item.comments() != null)
         for(int i = 0; i < item.comments().size(); i++)
         {
            ReaderComment comment = (ReaderComment)item.comments().get(i);
        
            sthComment.setLong(1, id);
            sthComment.setString(2, comment.id());
            sthComment.setString(3, comment.content());
            sthComment.setString(4, comment.html());
            sthComment.setString(5, comment.author());
            sthComment.setString(6, comment.user());
            sthComment.setString(7, comment.profile());
            sthComment.setString(8, comment.params());
            sthComment.setString(9, comment.venue());
            sthComment.setLong(10, comment.created());
            sthComment.setLong(11, comment.modified());
            sthComment.addBatch();
         }

      dbh.setAutoCommit(false);
      sthItem.executeBatch();
      sthOrigin.executeBatch();
      sthLink.executeBatch();
      sthUser.executeBatch();
      sthComment.executeBatch();
      dbh.setAutoCommit(true);

      s = dbh.createStatement() ;
      r = s.executeQuery("select count(id) from item");
      while(r.next())
	 System.out.println("Item table has: " + r.getString(1));
      r.close();

      dbh.close();

      return true;
   }

   public long fromTag(String tag)
   {
      String[] parts = tag.split("item/", 2);
      String hex = parts[1];

      if(hex.length() < 16)
         while(hex.length() < 16)
            hex = "0" + hex;

      String l8 = hex.substring(hex.length() - 8, hex.length());
      String u8 = hex.substring(0, 8);
   
      return ((Long.parseLong(u8, 16) << 32) | Long.parseLong(l8, 16));
   }

   // Satisfy abstract conditions for now.
   public int rowsInTable(String table) { return 0; }
   public boolean tidy(long time) { return true; }
}
